﻿Imports System.Data.SqlClient
Imports BBI_FE_Mapping.MapDataset


Public Class collElencoSource
    Inherits List(Of clsSource)
End Class

Public Class collElencoFiltri
    Inherits List(Of clsFiltro)
End Class

Public Class collElencoDimensioni
    Inherits List(Of clsDimensioni)
End Class

Public Class clsDimensioni

    Public Property Prefix As String
    Public Property Key As String

End Class

Public Class clsSourceRow
    Public Property Id_Mapping As Integer
    Public Property DESCR_ROW As String
    Public Property VALUE_SOURCE As String
    Public Property Stato As Integer '0 niente, 1 eliminato, 2 aggiunto
    Public Property StatoOri As Integer '0 niente, 1 eliminato, 2 aggiunto
    Public Property CheckStato As Integer = 0  '0 ok , 1 duplicato
    Public Property DescrCheck As String
End Class


Public Class clsNewTarget
    Public Property Id_Mapping As Integer
    Public Property DESCR_ROW As String
    Public Property VALUE_TARGET As String

End Class

Public Class collElencoSourcerOW
    Inherits List(Of clsSourceRow)
End Class



Public Class collElencoSourcerOWColl

    Inherits List(Of collElencoSourcerOW)
    Public Property Num As Integer

End Class



Public Class clsSource

    Public Property Prefix As String = ""
    Public Property v_ElencoSource As String
    Public Property v_ElencoRowSourceMap As String
    Public Property sqlANA_ConFiltri As String
    Public Property RicercaLibera As Boolean

    Public Property Num As String
    Public Property ID_TYPE_MAPPING As Long

    Public Function GetDsElencoSourceRow(ByVal pID_mapping As Integer) As collElencoSourcerOW
        Dim lsql As String
        Dim lcoll As New collElencoSourcerOW


        lsql = "IF EXISTS ("
        lsql = lsql & " SELECT VALUE_SOURCE FROM BBI_MAPPING.BBI_MAPPING.DATA_DWH_MODEL_ANA_MAPPING WHERE ID_MAPPING = " + pID_mapping.ToString
        lsql = lsql & " AND VALUE_SOURCE = '(ALL)'"
        lsql = lsql & " AND FIELD_SOURCE = '" + Prefix + "'"
        lsql = lsql & ")"
        lsql = lsql & " BEGIN"
        lsql = lsql & " select " + pID_mapping.ToString + " AS ID_MAPPING, '(ALL)' AS DESCR_ROW, '(ALL)' AS VALUE_SOURCE"
        lsql = lsql & " END"
        lsql = lsql & " ELSE"
        lsql = lsql & " BEGIN"
        lsql = lsql & " " + v_ElencoRowSourceMap
        lsql = lsql & " AND ID_mapping = " + pID_mapping.ToString
        lsql = lsql & " END"


        'ora devo impostare il datasource della griglia
        'originale
        'lsql = v_ElencoRowSourceMap

        '        SELECT id_mapping, BBI_DWH_MASTER_DATA.BBI_DWH_MASTER_DATA.t_ANA_CONTO_CONTABILE.v_cod + ' - '  + V_DESCR AS DESCR_ROW,
        'BBI_DWH_MASTER_DATA.BBI_DWH_MASTER_DATA.t_ANA_CONTO_CONTABILE.i_MEM_ID AS VALUE_SOURCE 
        ' FROM BBI_MAPPING.BBI_MAPPING.DATA_DWH_MODEL_ANA_MAPPING, BBI_DWH_MASTER_DATA.BBI_DWH_MASTER_DATA.t_ANA_CONTO_CONTABILE
        ' WHERE [ID_TYPE_MAPPING] = 11
        ' AND FIELD_SOURCE = 'i_ID_CONTO_CONTAB' AND VALUE_SOURCE = BBI_DWH_MASTER_DATA.BBI_DWH_MASTER_DATA.t_ANA_CONTO_CONTABILE.i_MEM_ID

        'lsql = lsql & " AND ID_mapping = " + pID_mapping.ToString

        '        SELECT id_mapping, BBI_DWH_MASTER_DATA.BBI_DWH_MASTER_DATA.t_ANA_CONTO_CONTABILE.v_cod + ' - '  + V_DESCR AS DESCR_ROW,
        'BBI_DWH_MASTER_DATA.BBI_DWH_MASTER_DATA.t_ANA_CONTO_CONTABILE.i_MEM_ID AS VALUE_SOURCE 
        ' FROM BBI_MAPPING.BBI_MAPPING.DATA_DWH_MODEL_ANA_MAPPING, BBI_DWH_MASTER_DATA.BBI_DWH_MASTER_DATA.t_ANA_CONTO_CONTABILE
        ' WHERE [ID_TYPE_MAPPING] = 11
        ' AND FIELD_SOURCE = 'i_ID_CONTO_CONTAB' AND VALUE_SOURCE = BBI_DWH_MASTER_DATA.BBI_DWH_MASTER_DATA.t_ANA_CONTO_CONTABILE.i_MEM_ID AND ID_mapping = 50



        Using connection As New SqlConnection(My.Settings.BBI_MAPPINGConnectionString)


            Dim adapter As SqlDataAdapter = New SqlDataAdapter(lsql, connection)

            Dim tb As DataSet = New DataSet
            adapter.Fill(tb, "defualt")

            For Each r As DataRow In tb.Tables("defualt").Rows
                Dim ld As New clsSourceRow
                ld.DESCR_ROW = CStr(r("DESCR_ROW"))
                ld.VALUE_SOURCE = CStr(r("VALUE_SOURCE"))
                ld.Id_Mapping = pID_mapping
                lcoll.Add(ld)
            Next

            GetDsElencoSourceRow = lcoll

        End Using


    End Function


    Public Function GetDsElencoSource(ByVal pfiltro As String, ByVal pNewMapping As String) As DataSet
        Dim lsql As String

        'pNewMapping
        '0 non è un nuovo mapping quindi si cercano i target tra quelli in mapping
        '1 è nuovo si cerca tra tutta l'anagrafica

        lsql = "SELECT DISTINCT 'false' AS FILTRA , t.* from ( " & v_ElencoSource & " ) t"

        If pNewMapping = "0" Then
            lsql = lsql & " , BBI_MAPPING.BBI_MAPPING.DATA_DWH_MODEL_ANA_MAPPING ANA"
        End If

        lsql = lsql & " WHERE DESCR LIKE '%" & pfiltro & "%'"
        lsql = lsql & " AND DESCR <> '(all)'"

        'ora devo impostare il datasource della griglia
        If pNewMapping = "0" Then
            lsql = lsql & " AND T.MEM_ID = ANA.VALUE_SOURCE"
            lsql = lsql & " AND ANA.FIELD_SOURCE = '" + Me.Prefix + "'"
            lsql = lsql & " AND ID_TYPE_MAPPING = " + Me.ID_TYPE_MAPPING.ToString + ""

        End If
        Try

            Using connection As New SqlConnection(My.Settings.BBI_MAPPINGConnectionString)


                Dim adapter As SqlDataAdapter = New SqlDataAdapter(lsql, connection)

                Dim tb As DataSet = New DataSet

                adapter.Fill(tb, "defualt")

                GetDsElencoSource = tb
            End Using

        Catch ex As Exception
            Throw ex
        End Try

    End Function


End Class


Public Class clsFiltro

    Public Property Prefix As String = ""
    Public Property Mem_ID As String
    Public Property Mem_Code As String 'probabilmente non servirà a nulla
    Public Property mem_Desc As String
    Public Property Caricato As Boolean
    Public Property IsTarget As Boolean

End Class

Public Class dbHelp

    Private _connection As Global.System.Data.SqlClient.SqlConnection

    Public Sub AlterView(ByVal pView As String, ByVal pSql As String)

        'Dim lsqlDrop As String = ""
        'Dim lsql As String = ""

        'Exit Sub
        'lsqlDrop = " IF  EXISTS (SELECT * FROM sys.views WHERE object_id = OBJECT_ID(N'[BwAppMap].[" + pView + "]'))"
        'lsqlDrop = lsqlDrop & "DROP VIEW [BwAppMap].[" + pView + "]" + vbCrLf

        'lsql = " create VIEW BwAppMap." + pView + vbCrLf
        'lsql = lsql + " AS " + vbCrLf
        'lsql = lsql + pSql

        'Using connection As New SqlConnection(my.Settings.BBI_MAPPINGConnectionString)

        '    Dim command As New SqlCommand(lsqlDrop, connection)
        '    command.Connection.Open()
        '    command.ExecuteNonQuery()
        '    command.CommandText = lsql
        '    command.ExecuteNonQuery()

        'End Using


    End Sub


    Public Function ExistView(ByVal pDatabase As String, ByVal pView As String) As Boolean

        Dim lsql As String
        'Dim tb As DataSet
        lsql = "use " + pDatabase
        lsql = lsql & " select * from "
        lsql = lsql & " sys.views WHERE "
        lsql = lsql & " object_id = OBJECT_ID(N'" + pView + "')"

        'ora devo impostare il datasource della griglia

        Using connection As New SqlConnection(My.Settings.BBI_MAPPINGConnectionString)


            Dim adapter As SqlDataAdapter = New SqlDataAdapter(lsql, connection)

            Dim tb As DataSet = New DataSet
            adapter.Fill(tb, "defualt")
            ExistView = tb.Tables(0).Rows.Count > 0

        End Using


    End Function


    Public Function ExistsRows(ByVal pSql As String) As Boolean


        Using connection As New SqlConnection(My.Settings.BBI_MAPPINGConnectionString)


            Dim adapter As SqlDataAdapter = New SqlDataAdapter(pSql, connection)

            Dim tb As DataSet = New DataSet
            adapter.Fill(tb, "defualt")
            ExistsRows = tb.Tables(0).Rows.Count > 0

        End Using


    End Function


End Class

Public Class clsMap

    Public ElencoSource As collElencoSource
    Public PrefixTarget As String
    Private Const TBaNAmAP As String = "BBI_MAPPING.BBI_MAPPING."
    Private _TbTarget As String
    Private _FieldTarget As String = ""
    Private _FieldTargetDesc As String = ""
    Private _ElencoTarget As String
    Private _TbSource As String
    Private id_type_mapping As Integer

    Private Const K_SQL_SOURCE_ANA As String = "SELECT VALUE_SOURCE as id__PREFIX_ , VALUE_TARGET as VALUE_TARGET__PREFIX_" & _
        "   , ID_MAPPING as ID_MAPPING__PREFIX_" + _
        " FROM [BBI_MAPPING].[BBI_MAPPING].[DATA_DWH_MODEL_ANA_MAPPING] " + _
        " WHERE  [ID_TYPE_MAPPING] = _ID_TYPE_MAPPING_ and FIELD_SOURCE = '_PREFIX_'"



    Public Property v_grid_map As String 'query per riempire la griglia mapping
    Public NumSource As Integer



    Public Function SetDsSourceRowCheckStato(ByVal pdss As collElencoSourcerOW, ByVal ptb As DataTable) As collElencoSourcerOW
        'pdss la collezione dei source in edit da settare con mapping esistenti
        ' pdss.Item(0).
        Return Nothing

    End Function


    Public Function getPrefixSource(ByVal pNum As Integer) As String

        getPrefixSource = ""

        For Each s In ElencoSource
            If pNum.ToString = s.Num Then
                getPrefixSource = s.Prefix
                Exit For
            End If

        Next

    End Function


    Public Function GetDsElencoTarget(ByVal pfiltro As String, ByVal pNewMapping As String) As DataSet

        'pNewMapping
        '0 non è un nuovo mapping quindi si cercano i target tra quelli in mapping
        '1 è nuovo si cerca tra tutta l'anagrafica

        Dim lsql As String

        lsql = "SELECT DISTINCT 'false' AS FILTRA , t.* from ( " & _ElencoTarget & " ) t"

        If pNewMapping = "0" Then
            lsql = lsql & " , BBI_MAPPING.BBI_MAPPING.DATA_DWH_MODEL_ANA_MAPPING ANA"
        End If

        lsql = lsql & " WHERE DESCR LIKE '%" & pfiltro & "%'"
        If pNewMapping = "0" Then
            lsql = lsql & " AND T.MEM_ID = ANA.VALUE_TARGET"
            ''lsql = lsql & " AND ANA.FIELD_SOURCE = '" + "" + "'"
            lsql = lsql & " AND ID_TYPE_MAPPING = " + Me.id_type_mapping.ToString + ""

        End If

        'ora devo impostare il datasource della griglia

        Using connection As New SqlConnection(My.Settings.BBI_MAPPINGConnectionString)


            Dim adapter As SqlDataAdapter = New SqlDataAdapter(lsql, connection)

            Dim tb As DataSet = New DataSet
            adapter.Fill(tb, "defualt")
            GetDsElencoTarget = tb
        End Using


    End Function


    Private Function GetTbFieldTarget(ByVal pField As String) As String
        Return _TbTarget & "." & pField
    End Function

    Private Function GetTbFieldSource(ByVal pField As String) As String
        Return _TbSource & "." & pField
    End Function






    Public Function getDSDimensioni(ByVal pid_type_mapping As Integer) As collElencoDimensioni

        Dim lsql As String
        Dim lcoll As New collElencoDimensioni
        lsql = "SELECT [FIELD_FACT_SOURCE] as prefix , '_Source_' + FIELD_FACT_SOURCE as [Key] FROM [BBI_MAPPING].[BBI_MAPPING].[DATA_DWH_MODEL_DETT_DIM_TYPE_MAPPING]"
        lsql = lsql & " WHERE ID_TYPE_MAPPING = " & pid_type_mapping.ToString
        lsql = lsql + " UNION "
        lsql = lsql + "SELECT [PREFIX_TARGET] as prefix , '_Target_' + PREFIX_TARGET as [Key] FROM [BBI_MAPPING].[BBI_MAPPING].[DATA_DWH_MODEL_DETT_DIM_TYPE_MAPPING]"

        lsql = lsql & " WHERE ID_TYPE_MAPPING = " & pid_type_mapping.ToString

        Using connection As New SqlConnection(My.Settings.BBI_MAPPINGConnectionString)


            Dim adapter As SqlDataAdapter = New SqlDataAdapter(lsql, connection)

            Dim tb As DataSet = New DataSet
            adapter.Fill(tb, "ciccio")
            For Each r As DataRow In tb.Tables("ciccio").Rows
                Dim ld As New clsDimensioni
                ld.Prefix = CStr(r("Prefix"))
                ld.Key = CStr(r("Key"))

                lcoll.Add(ld)

            Next
            getDSDimensioni = lcoll
        End Using

    End Function

    Public Function GetMaxIdMapping() As Integer


        Dim lsql As String
        lsql = "SELECT   ISNULL(MAX(ID_MAPPING), 0)FROM BBI_MAPPING.DATA_DWH_MODEL_ANA_MAPPING WHERE ID_TYPE_MAPPING = " + id_type_mapping.ToString

        Using connection As New SqlConnection(My.Settings.BBI_MAPPINGConnectionString)


            Dim adapter As SqlDataAdapter = New SqlDataAdapter(lsql, connection)

            Dim tb As DataSet = New DataSet
            adapter.Fill(tb, "ciccio")
            GetMaxIdMapping = CInt(tb.Tables(0).Rows(0).Item(0))
        End Using



    End Function

    Public Function getDSDimensioniSource(ByVal pid_type_mapping As Integer) As DataSet

        Dim lsql As String
        lsql = "SELECT [FIELD_FACT_SOURCE] as prefix , 0 as IsTarget FROM [BBI_MAPPING].[BBI_MAPPING].[DATA_DWH_MODEL_DETT_DIM_TYPE_MAPPING]"
        'lsql = lsql + " UNION "
        'lsql = lsql + "SELECT [PREFIX_TARGET] as prefix , 1 as IsTarget FROM [BBI_MAPPING].[BBI_MAPPING].[DATA_DWH_MODEL_DETT_DIM_TYPE_MAPPING]"

        lsql = lsql & " WHERE ID_TYPE_MAPPING = " & pid_type_mapping.ToString

        Using connection As New SqlConnection(My.Settings.BBI_MAPPINGConnectionString)


            Dim adapter As SqlDataAdapter = New SqlDataAdapter(lsql, connection)

            Dim tb As DataSet = New DataSet
            adapter.Fill(tb, "ciccio")
            getDSDimensioniSource = tb
        End Using

    End Function

    Public Sub Inizializza(ByVal pid_type_mapping As Integer)
        'qua devo inizializzare le viste attraverso delle alter view di quello esistente.
        Dim tbDet As New MapDatasetTableAdapters.DATA_DWH_MODEL_DETT_DIM_TYPE_MAPPINGTableAdapter
        Dim lRows As DataRowCollection
        Dim ldbHelp As New dbHelp
        Dim lSelectSource As String = ""
        Dim lsql As String
        Dim lsqlAnagrafica As String = ""

        ElencoSource = New collElencoSource

        id_type_mapping = pid_type_mapping
        lRows = tbDet.GetData(pid_type_mapping).Rows
        NumSource = lRows.Count
        If lRows.Count > 0 Then
            Dim r As DATA_DWH_MODEL_DETT_DIM_TYPE_MAPPINGRow = DirectCast(lRows(0), DATA_DWH_MODEL_DETT_DIM_TYPE_MAPPINGRow)

            If ldbHelp.ExistView(r.DB_NAME_TARGET, r.SCHEMA_NAME_TARGET & ".vm_" & r.ANA_TABLE_NAME_TARGET) Then
                _TbTarget = r.DB_NAME_TARGET & "." & r.SCHEMA_NAME_TARGET & ".vm_" & r.ANA_TABLE_NAME_TARGET
            Else
                _TbTarget = r.DB_NAME_TARGET & "." & r.SCHEMA_NAME_TARGET & "." & r.ANA_TABLE_NAME_TARGET
            End If


            '_TbTarget = r.DB_NAME_TARGET & "." & r.SCHEMA_NAME_TARGET & "." & r.ANA_TABLE_NAME_TARGET
            _FieldTarget = r.FIELD_TARGET_CODE
            _FieldTargetDesc = r.FIELD_TARGET_DESC
            PrefixTarget = r.PREFIX_TARGET
        End If
        lsql = "SELECT " + GetTbFieldTarget(_FieldTarget) + " AS MEM_ID,"
        lsql = lsql & GetTbFieldTarget(_FieldTargetDesc) & " AS DESCR"
        lsql = lsql & " FROM " & _TbTarget
        _ElencoTarget = lsql

        Dim i As Integer
        lsql = ""
        lsql = lsql & " Select id_MAPPING"
        lsql = lsql & " , '' AS DESCR_ROW"
        lsql = lsql & " , 0 AS VALUE_SOURCE"
        lsql = lsql & " from [BBI_MAPPING].BBI_MAPPING.DATA_DWH_MODEL_ANA_MAPPING "
        lsql = lsql & " WHERE ID_TYPE_MAPPING = " & pid_type_mapping.ToString


        'costruisco tutte le viste per le colonne delle griglie
        For i = 1 To 9
            ldbHelp.AlterView("v_ElencoRowSourceMap_" & i.ToString, lsql)
        Next

        i = 0
        For Each r As DATA_DWH_MODEL_DETT_DIM_TYPE_MAPPINGRow In lRows
            i = i + 1


            If ldbHelp.ExistView(r.DB_NAME_SOURCE, r.SCHEMA_NAME_SOURCE & ".vm_" & r.ANA_TABLE_NAME_SOURCE) Then

                _TbSource = r.DB_NAME_SOURCE & "." & r.SCHEMA_NAME_SOURCE & ".vm_" & r.ANA_TABLE_NAME_SOURCE

            Else

                _TbSource = r.DB_NAME_SOURCE & "." & r.SCHEMA_NAME_SOURCE & "." & r.ANA_TABLE_NAME_SOURCE

            End If

            Dim lSource As New clsSource

            'altero la vista elenco.
            lsql = ""

            lsql = "SELECT CAST(" & GetTbFieldSource(r.FIELD_ANA_SOURCE) & " AS VARCHAR) AS MEM_ID,"
            lsql = lsql & GetTbFieldSource(r.FIELD_ANA_SOURCE_DESC) & " AS DESCR"
            lsql = lsql & " FROM " & _TbSource

            ldbHelp.AlterView("v_ElencoSource_" & i.ToString, lsql)

            lSource.v_ElencoSource = lsql + " UNION SELECT '(ALL)' , '(ALL)'"


            'altero la vista elenco mappato nella riga della griglia.
            lsql = ""

            lsql = "SELECT distinct id_mapping, " & GetTbFieldSource(r.FIELD_ANA_SOURCE_DESC) & " AS DESCR_ROW," + vbCrLf
            lsql = lsql & GetTbFieldSource(r.FIELD_ANA_SOURCE) + " AS VALUE_SOURCE " + vbCrLf
            lsql = lsql & " FROM BBI_MAPPING.BBI_MAPPING.DATA_DWH_MODEL_ANA_MAPPING, " & _TbSource + vbCrLf
            lsql = lsql & " WHERE [ID_TYPE_MAPPING] = " + pid_type_mapping.ToString + vbCrLf
            lsql = lsql & " AND FIELD_SOURCE = '" + r.FIELD_FACT_SOURCE + "'"
            lsql = lsql & " AND VALUE_SOURCE = CAST(" + GetTbFieldSource(r.FIELD_ANA_SOURCE) + " AS VARCHAR)"
            'ldbHelp.AlterView("v_ElencoRowSourceMap_" & i.ToString, lsql)
            lSource.v_ElencoRowSourceMap = lsql
            lSource.ID_TYPE_MAPPING = pid_type_mapping
            lSource.Prefix = r.FIELD_FACT_SOURCE
            lSource.Num = i.ToString
            ElencoSource.Add(lSource)

            lSelectSource = lSelectSource + " '" + r.FIELD_ANA_SOURCE + "' as TARGET_" + i.ToString + ", "

        Next r

        'Creazione della vista datasource dell griglia
        lsql = "SELECT DISTINCT " + lSelectSource + " id_mapping, value_target,  " + vbCrLf
        lsql = lsql + GetTbFieldTarget(_FieldTargetDesc) + " as DESC_TARGET"
        lsql = lsql + " FROM [BBI_MAPPING].[BBI_MAPPING].[DATA_DWH_MODEL_ANA_MAPPING] , " + vbCrLf
        lsql = lsql + _TbTarget + " " + vbCrLf
        lsql = lsql + " WHERE [ID_TYPE_MAPPING] = " + pid_type_mapping.ToString + vbCrLf
        lsql = lsql + " AND value_target = " + GetTbFieldTarget(_FieldTarget)
        ldbHelp.AlterView("v_grid_map", lsql)
        v_grid_map = lsql



    End Sub

    Public Function DeleteMapping(ByVal pid_mapping As Integer) As Boolean



        Using connection As New SqlConnection(My.Settings.BBI_MAPPINGConnectionString)

            connection.Open()

            Dim tr As SqlTransaction = connection.BeginTransaction

            Dim lsql As String
            lsql = "DELETE FROM BBI_MAPPING.DATA_DWH_MODEL_ANA_MAPPING WHERE ID_MAPPING = " + pid_mapping.ToString + " AND ID_TYPE_MAPPING = " + id_type_mapping.ToString

            Try


                Dim command As New SqlCommand(lsql, connection, tr)
                command.ExecuteNonQuery()

                tr.Commit()
                DeleteMapping = True
            Catch ex As Exception
                tr.Rollback()
                DeleteMapping = False
            End Try

        End Using

    End Function


    Public Function SaveMapping(ByVal plist As collElencoFiltri, ByRef pid_mapping As Integer) As Boolean
        'il tipo mapping ce l'ho in class ed è id_type_mapping

        'setto i target ed i source
        Dim Target As clsFiltro = Nothing

        For Each s As clsFiltro In plist
            If s.IsTarget Then
                Target = s
            End If
        Next
        plist.Remove(Target)
        SaveMapping = False

        Dim lsql As String
        Dim lsql2 As String
        Using connection As New SqlConnection(My.Settings.BBI_MAPPINGConnectionString)

            If pid_mapping = -1 Then

                pid_mapping = GetMaxIdMapping() + 1

            End If

            connection.Open()
            Dim tr As SqlTransaction = connection.BeginTransaction

            Try

                'ricavo l'id se in inserimento altrimenti uso quello corrente

                lsql = "DELETE FROM BBI_MAPPING.DATA_DWH_MODEL_ANA_MAPPING WHERE ID_MAPPING = " + pid_mapping.ToString + " AND ID_TYPE_MAPPING = " + id_type_mapping.ToString

                Dim command As New SqlCommand(lsql, connection, tr)
                command.ExecuteNonQuery()

                For Each Source As clsFiltro In plist

                    lsql = "INSERT INTO BBI_MAPPING.DATA_DWH_MODEL_ANA_MAPPING"
                    lsql = lsql + " ( "
                    lsql2 = " VALUES ("
                    lsql = lsql + " APPL_ID, "
                    lsql2 = lsql2 + " 144,"
                    lsql = lsql + " ID_TYPE_MAPPING, "
                    lsql2 = lsql2 + Me.id_type_mapping.ToString + ","
                    lsql = lsql + " FIELD_SOURCE, "
                    lsql2 = lsql2 + "'" + Source.Prefix + "',"
                    lsql = lsql + " VALUE_SOURCE, "
                    lsql2 = lsql2 + "'" + Source.Mem_ID + "',"
                    lsql = lsql + " PREFIX_TARGET, "
                    lsql2 = lsql2 + "'" + Target.Prefix + "',"
                    lsql = lsql + " VALUE_TARGET, "
                    lsql2 = lsql2 + "'" + Target.Mem_ID + "',"
                    lsql = lsql + " ID_MAPPING "
                    lsql2 = lsql2 + pid_mapping.ToString + ""
                    lsql = lsql + " ) "
                    lsql2 = lsql2 + " ) "
                    lsql = lsql + lsql2

                    command.CommandText = lsql
                    command.ExecuteNonQuery()

                Next Source


                tr.Commit()
                SaveMapping = True
            Catch ex As Exception
                'errore nel salvataggio
                tr.Rollback()

            End Try


        End Using






    End Function

    Public Function GetDsMapping(ByVal pList As List(Of clsFiltro), ByVal pRicerca As String, ByVal pID As String) As DataSet

        Dim lsql As String = GetQueryAnagrafica(pList, False, pRicerca, pID, -999)

        Try

            Using connection As New SqlConnection(My.Settings.BBI_MAPPINGConnectionString)


                Dim adapter As SqlDataAdapter = New SqlDataAdapter(lsql, connection)

                Dim tb As DataSet = New DataSet
                adapter.Fill(tb, "defualt")
                GetDsMapping = tb
            End Using
        Catch ex As Exception
            Throw ex
        End Try



    End Function



    Public Function GetDsMappingCheck(ByVal pList As List(Of clsFiltro), ByVal pID As Integer) As DataSet

        Dim lsql As String = GetQueryAnagrafica(pList, False, "", "", pID)


        lsql = "SELECT * FROM (" + lsql + ") A"
        lsql = lsql + " where ID_MAPPING not in ("
        lsql = lsql + " select ID_MAPPING"
        lsql = lsql + " from [BBI_MAPPING].[BBI_MAPPING].[DATA_DWH_MODEL_ANA_MAPPING] "
        lsql = lsql + " where ID_TYPE_MAPPING = " + id_type_mapping.ToString
        lsql = lsql + " and VALUE_SOURCE = '(ALL)'"
        lsql = lsql + " )"

        Try

            Using connection As New SqlConnection(My.Settings.BBI_MAPPINGConnectionString)


                Dim adapter As SqlDataAdapter = New SqlDataAdapter(lsql, connection)

                Dim tb As DataSet = New DataSet
                adapter.Fill(tb, "defualt")
                GetDsMappingCheck = tb
            End Using
        Catch ex As Exception
            Throw ex
        End Try



    End Function




    Public Function GetDsMappingNew(ByVal pNewTarget As clsNewTarget) As DataSet

        Dim lsql As String = GetQueryAnagraficaNew(pNewTarget)

        Try

            Using connection As New SqlConnection(My.Settings.BBI_MAPPINGConnectionString)


                Dim adapter As SqlDataAdapter = New SqlDataAdapter(lsql, connection)

                Dim tb As DataSet = New DataSet
                adapter.Fill(tb, "defualt")
                GetDsMappingNew = tb
            End Using
        Catch ex As Exception
            Throw ex
        End Try



    End Function




    Public Function GetDsCountMapping(ByVal pList As List(Of clsFiltro), ByVal pRicerca As String, ByVal pID As String) As DataSet

        Dim lsql As String = GetQueryAnagrafica(pList, True, pRicerca, pID, -999)


        Using connection As New SqlConnection(My.Settings.BBI_MAPPINGConnectionString)


            Dim adapter As SqlDataAdapter = New SqlDataAdapter(lsql, connection)

            Dim tb As DataSet = New DataSet
            adapter.Fill(tb, "defualt")
            GetDsCountMapping = tb
        End Using


    End Function

    Public Function GetQueryAnagrafica(ByVal pList As List(Of clsFiltro), ByVal pCount As Boolean, ByVal pRicerca As String, ByVal pIDCerca As String, ByVal pid_mapping As Integer) As String

        'se l'id mapping è -999 allora si tratta di una ricerca normale altrimenti è un caso di verifica in fase di salvataggio
        ' e in questo caso sarà necessario aggiungere la parte in where che id mapping <> dal parametro.
        '

        Dim lsql As String = ""
        Dim lsRic As String = ""
        Dim lhasFiltro As Boolean
        Dim lsIn As String
        Dim lHelp As New dbHelp
        If pList Is Nothing Or pRicerca <> "" Or pIDCerca <> "" Then
            pList = New collElencoFiltri
        End If
        Dim lbRicercaRows As Boolean = False
        If pRicerca <> "" Then


        End If


        For Each lsource As clsSource In ElencoSource
            'se questo prefisso esiste nei filtri lo inserisco
            lhasFiltro = False

            For Each lfiltro As clsFiltro In pList
                If lfiltro.Prefix = lsource.Prefix And Not lfiltro.IsTarget Then
                    'ok esiste
                    lhasFiltro = True
                    Exit For

                End If
            Next
            'If lhasFiltro Then
            'faccio il replace di quello che mi serve
            lsql = K_SQL_SOURCE_ANA
            lsql = lsql.Replace("_PREFIX_", lsource.Prefix)
            lsql = lsql.Replace("_ID_TYPE_MAPPING_", id_type_mapping.ToString)
            lsource.sqlANA_ConFiltri = ""

            If lhasFiltro Then
                lsql = lsql + " AND VALUE_SOURCE in( "

                lsIn = ""
                For Each lfiltro As clsFiltro In pList
                    If lfiltro.Prefix = lsource.Prefix Then
                        lsIn = lsIn + "'" + lfiltro.Mem_ID.ToString + "',"
                    End If
                Next
                lsIn = lsIn.Substring(0, lsIn.Length - 1)


                lsql = lsql + lsIn + ")"
            End If

            If IsNumeric(pIDCerca) Then
                lsql = lsql + " AND ID_MAPPING = " + pIDCerca
            End If

            lsource.RicercaLibera = False
            If pRicerca <> "" Then

                If pRicerca = "(ALL)" Then

                Else
                    'qua dovrei andare in join con l'anagrafica del mapping
                    lsRic = "  (SELECT CAST(MEM_ID AS VARCHAR) AS MEM_ID FROM (" + lsource.v_ElencoSource + ") A"
                    lsRic = lsRic + " WHERE DESCR LIKE '%" + pRicerca + "%')"
                End If
                lsRic = " (SELECT CAST(MEM_ID AS VARCHAR) AS MEM_ID FROM "
                lsRic = lsRic + "  ("

                lsRic = lsRic + "  (SELECT CAST(MEM_ID AS VARCHAR) AS MEM_ID FROM (" + lsource.v_ElencoSource + ") A"
                lsRic = lsRic + " WHERE DESCR LIKE '%" + pRicerca + "%')"
                lsRic = lsRic + "  ) b"
                lsRic = lsRic + "  inner join BBI_MAPPING.BBI_MAPPING.DATA_DWH_MODEL_ANA_MAPPING ana"
                lsRic = lsRic + "  on b.MEM_ID = ana.VALUE_SOURCE "
                lsRic = lsRic + "  where ana.FIELD_SOURCE = '" + lsource.Prefix + "'"
                lsRic = lsRic + "  )"
                If lHelp.ExistsRows("SELECT * FROM " + lsRic + " s") Then

                    lsql = lsql + " AND VALUE_SOURCE IN (SELECT CAST(MEM_ID AS VARCHAR) AS MEM_ID  FROM (" + lsource.v_ElencoSource + ") A"
                    lsql = lsql + " WHERE DESCR LIKE '%" + pRicerca + "%')"
                    lsource.RicercaLibera = True
                    lbRicercaRows = True
                End If
            End If

            lsource.sqlANA_ConFiltri = lsql

            'Else
            'lsource.sqlANA_ConFiltri = ""

            'End If 'If lhasFiltro Then



        Next

        Dim lSelect As String

        lSelect = ""

        Dim ltbTarget As String = ""
        Dim lfield_id_mapping As String = ""
        For Each lsource As clsSource In ElencoSource
            'If lsource.sqlANA_ConFiltri <> "" Then
            lSelect = "SELECT VALUE_TARGET_i_id_conto_contab as VALUE_TARGET, ID_MAPPING_i_id_conto_contab as ID_MAPPING"
            lSelect = " SELECT DISTINCT VALUE_TARGET_" + lsource.Prefix + " as VALUE_TARGET, "
            lfield_id_mapping = " ID_MAPPING_" + lsource.Prefix
            lSelect = lSelect + lfield_id_mapping + " as ID_MAPPING, "

            ltbTarget = lsource.Prefix
            Exit For
            'End If
        Next

        For Each lsource As clsSource In ElencoSource
            'If lsource.sqlANA_ConFiltri <> "" Then
            If pid_mapping = -999 Then
                lSelect = lSelect + " '" + lsource.Prefix + "' as Source_" + lsource.Num + ","
            Else

                lSelect = lSelect + " ID_" + lsource.Prefix + " as Source_" + lsource.Num + ","
            End If

            'End If
        Next



        If pCount Then
            lSelect = lSelect.Substring(0, lSelect.Length - 1) + " FROM "

        Else
            lSelect = lSelect + GetTbFieldTarget(_FieldTargetDesc) + " as DESC_TARGET FROM "

        End If

        Dim lFrom As String = ""

        For Each lsource As clsSource In ElencoSource
            If lsource.sqlANA_ConFiltri <> "" Then
                lFrom = lFrom + "(" + lsource.sqlANA_ConFiltri + ") " + lsource.Prefix + ","
            End If
        Next

        If lFrom <> "" Then
            If pCount Then
                lFrom = lFrom.Substring(0, lFrom.Length - 1)
            Else
                lFrom = lFrom + _TbTarget
            End If
        End If


        Dim lwhere As String = " WHERE 1 = 1 and "
        If Not pCount Then
            lwhere = lwhere + ltbTarget + ".VALUE_TARGET_" + ltbTarget + " = " + GetTbFieldTarget(_FieldTarget)
            lwhere = lwhere + " AND "
        End If

        For Each lsource As clsSource In ElencoSource
            If lsource.sqlANA_ConFiltri <> "" Then
                For Each lsource2 As clsSource In ElencoSource
                    If lsource2.sqlANA_ConFiltri <> "" And lsource.Prefix <> lsource2.Prefix Then
                        lwhere = lwhere + lsource.Prefix + ".ID_MAPPING_" + lsource.Prefix
                        lwhere = lwhere + " = "
                        lwhere = lwhere + lsource2.Prefix + ".ID_MAPPING_" + lsource2.Prefix
                        lwhere = lwhere + " and "
                    End If
                Next
            End If
        Next

        lwhere = lwhere.Substring(0, lwhere.Length - 5)
        'verifico la presenza di filtri target


        'lsql = lsql + " AND VALUE_SOURCE in( "

        lsIn = ""
        For Each lfiltro As clsFiltro In pList
            If lfiltro.IsTarget Then
                lsIn = lsIn + "'" + lfiltro.Mem_ID.ToString + "',"
            End If
        Next
        If lsIn.Length > 0 Then
            lsIn = lsIn.Substring(0, lsIn.Length - 1)

            lwhere = lwhere + " and VALUE_TARGET_" + ltbTarget + " IN(" + lsIn + ")"
        End If

        'lsql = lsql + lsIn + ")"



        If pCount Then
            lsql = " SELECT COUNT( distinct " + lfield_id_mapping + " ) FROM "
            lsql = lsql & lFrom & " " & lwhere
        Else
            lsql = lSelect & lFrom & " " & lwhere
            If pRicerca <> "" And Not lbRicercaRows Then
                lsql = lsql + " and 1 = 0 "
            End If
            If pid_mapping <> -999 Then

                lsql = lsql + " and " + lfield_id_mapping + " <> " + pid_mapping.ToString
            End If


        End If

        Return lsql

    End Function





    Public Function GetQueryAnagraficaNew(ByVal pNewTarget As clsNewTarget) As String

        Dim lsql As String = ""
        Dim lSelect As String

        lSelect = ""


        lSelect = " SELECT DISTINCT '" + pNewTarget.VALUE_TARGET + "' as VALUE_TARGET, "
        lSelect = lSelect + " -1  as ID_MAPPING, "

        For Each lsource As clsSource In ElencoSource
            'If lsource.sqlANA_ConFiltri <> "" Then
            lSelect = lSelect + " '" + lsource.Prefix + "' as Source_" + lsource.Num + ","
        Next



        lSelect = lSelect + " '" + pNewTarget.DESCR_ROW + "' as DESC_TARGET  "

        lsql = lSelect

        Return lsql

    End Function



End Class
